create proc [dbo].[p_AverageDayConnectionCount](
	@Date datetime, 
	@DatabaseID bigint) AS  

set nocount on

declare @hours table(
		iHour int,
		iTotalSessions int default 0,
		iTotalConnections int default 0,
		iDBConnections int default 0) 
 
declare @iHour int,  		
	@dtDayBegins datetime,
	@dtDayEnds datetime,
	@iTotalSessions int,   
	@iTotalConnections int,    
	@iDBConnections int    

set @iHour=0  
while @iHour<24
begin  	
	set @dtDayBegins=dateadd(hour,@iHour,@Date)  	
	set @dtDayEnds=dateadd(minute,59,@dtDayBegins)

	insert into @hours(
		iHour,
		iTotalSessions,
		iTotalConnections,
		iDBConnections)
	select
		@iHour,
		sum(sessioncount),
		sum(serverconnections),
		sum(databaseconnections)
	from
		connectsummary a
	where
		a.databaseid=@DatabaseID
		and dbo.MakeDate(a.[Year],a.[Month],a.[Day],a.[Hour],0,0) between @dtDayBegins and @dtDayEnds

	set @iHour=@iHour+1  
end   
   
select   	
	a.iHour,
	dateadd(hh,a.iHour,@Date) 'dtWeekDate',
	isnull(convert(decimal(18,6),a.iTotalConnections)/convert(decimal(18,6),a.iTotalSessions),0) 'AvgCountOfAllConnections',
	isnull(convert(decimal(18,6),a.iDBConnections)/convert(decimal(18,6),a.iTotalSessions),0) 'AvgCountOfDBConnections'
from   	
	@hours a  		  
order by   	
	a.iHour 
   




